﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient

Public Class MonedaDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function existeMoneda(ByVal pDesMoneda As String) As Boolean
        Dim id As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM moneda " & _
                                            "WHERE DesMoneda = ?DesMoneda;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?DesMoneda", pDesMoneda)
                    myConn.Open()
                    id = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (id > 0)
        Catch ex As Exception
            Throw
        Finally
            id = Nothing
        End Try
    End Function

    Public Function registraMoneda(ByVal pMoneda As MonedaBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO moneda(DesMoneda, TipoCambio, Status, EnXML, DescrCentavos) " & _
                                            "VALUES (@DesMoneda, @TipoCambio, @Status, @xml, @descrCent);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@DesMoneda", pMoneda.DesMoneda)
                    myComm.Parameters.AddWithValue("@TipoCambio", pMoneda.TipoCambio)
                    Select Case pMoneda.Status
                        Case MonedaBE.EstatusMoneda.Activa
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case MonedaBE.EstatusMoneda.Inactiva
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myComm.Parameters.AddWithValue("@xml", pMoneda.ValorXML)
                    myComm.Parameters.AddWithValue("@descrCent", pMoneda.DescrCentavos)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaMoneda(ByVal pMoneda As MonedaBE, ByRef msg As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT count(*) " &
                                            "FROM moneda " & _
                                            "WHERE IdMoneda <> @id " &
                                                "AND  DesMoneda = @descripcion;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@id", pMoneda.IdMoneda)
                    myComm.Parameters.AddWithValue("@descripcion", pMoneda.DesMoneda)
                    myConn.Open()
                    If (Convert.ToInt16(myComm.ExecuteScalar()) > 0) Then
                        msg &= "Existe otro registro con la Moneda especificada." & vbCrLf
                        Return False
                    End If

                    myComm.CommandText = "UPDATE moneda " & _
                                            "SET DesMoneda = @DesMoneda, " & _
                                                "TipoCambio = @TipoCambio, " & _
                                                "Status = @Status, " & _
                                                "EnXML = @xml, " & _
                                                "DescrCentavos = @descrCentavos " & _
                                            "WHERE IdMoneda = @IdMoneda; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@DesMoneda", pMoneda.DesMoneda)
                    myComm.Parameters.AddWithValue("@TipoCambio", pMoneda.TipoCambio)
                    Select Case pMoneda.Status
                        Case MonedaBE.EstatusMoneda.Activa
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case MonedaBE.EstatusMoneda.Inactiva
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myComm.Parameters.AddWithValue("@xml", pMoneda.ValorXML)
                    myComm.Parameters.AddWithValue("@descrCentavos", pMoneda.DescrCentavos)
                    myComm.Parameters.AddWithValue("@IdMoneda", pMoneda.IdMoneda)
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaEstatusMoneda(ByVal pid As Integer, ByVal pSetActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE moneda " & _
                                            "SET Status = @Status " & _
                                            "WHERE IdMoneda = @IdMoneda; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pSetActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@IdMoneda", pid)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getMoneda(ByVal IdMoneda As Integer) As MonedaBE
        Dim tmpMoneda As MonedaBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdMoneda, DesMoneda, TipoCambio, Status, EnXML, DescrCentavos " & _
                                            "FROM moneda " & _
                                            "WHERE IdMoneda = @IdMoneda;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdMoneda", IdMoneda)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpMoneda = MonedaFact.Build(myDR)
                        Else
                            tmpMoneda = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpMoneda

        Catch ex As Exception
            Throw
        Finally
            tmpMoneda = Nothing
        End Try
    End Function

    Public Function getListadoMoneda(ByVal pMoneda As String, ByVal tipoEstatus As Enumeraciones.estatusParaBusquedas) As List(Of MonedaBE)
        Dim MonedaLs As List(Of MonedaBE) = New List(Of MonedaBE)
        Dim qryEstatus As String = ""
        Select Case tipoEstatus
            Case Enumeraciones.estatusParaBusquedas.Todos
                'No aplica filtro
            Case Enumeraciones.estatusParaBusquedas.Activos
                qryEstatus = " AND Status = 'A' "
            Case Enumeraciones.estatusParaBusquedas.Inactivos
                qryEstatus = " AND Status = 'I' "
        End Select

        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdMoneda, DesMoneda, TipoCambio, Status, EnXML, DescrCentavos " & _
                                           "FROM moneda " & _
                                           "WHERE DesMoneda LIKE @DesMoneda " &
                                           qryEstatus & ";"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@DesMoneda", "%" & pMoneda & "%")
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Moneda As MonedaBE = MonedaFact.Build(myDR)
                            If (Not Moneda Is Nothing) Then
                                MonedaLs.Add(Moneda)
                            End If
                            Moneda = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return MonedaLs
        Catch ex As Exception
            Throw
        Finally
            MonedaLs = Nothing
            qryEstatus = Nothing
        End Try
    End Function

#End Region

End Class
